/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package datos;
import com.mysql.jdbc.CommunicationsException;
import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import com.mysql.jdbc.exceptions.MySQLSyntaxErrorException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Marcelo
 */

public class DbConnection {
    private Connection connection=null;//conexion con la base de datos
    private String hostName="";//nombre del host 
    private String database="";//nombre de la db
    private String username="";//usuario
    private String url="";//url para la conexion con la db

    public String getDatabase() {
        return database;
    }

    public String getHostName() {
        return hostName;
    }

    public String getUrl() {
        return url;
    }

    public String getUsername() {
        return username;
    }
    
    public Connection connect(String hostName, String database, String username, String password)
    {
        try {

            this.hostName = hostName;
            this.database = database;
            this.username = username;

            Class.forName("com.mysql.jdbc.Driver");
            url = "jdbc:mysql://" + hostName + "/" + database;           

            connection = DriverManager.getConnection(url, username, password);
            

            return connection;
        }
        catch (CommunicationsException ex) {//servidor no encendido
            System.out.println("No se pudo conectar a la base de datos. Esta apagada?");
            //ex.printStackTrace();
            return null;
        }
        catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        }
    }

    public DbConnection()
    {
        
    }
    public DbConnection(String hostName, String database, String username, String password)
    {
        connect(hostName, database, username, password);
    }

    public Connection getConnection(){
        return connection;
    }

    public Connection closeConnection(){
        try {
            if (connection != null)
            {
                connection.close();
                //System.out.println("Conexión cerrada con " + url);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        connection=null;
        return connection;
    }

    //para los insert, delete y update
    public int execSql(String sql) throws MySQLSyntaxErrorException, MySQLIntegrityConstraintViolationException
    {
        try {
            if (connection == null)
                return 1;
            System.out.println("SQL :: " + sql);
            Statement s = connection.createStatement();
            //s.execute(sql);
            
            //s.executeUpdate(sql);
            return s.execute( sql ) == true?1:0;
        } catch (MySQLSyntaxErrorException ex) {
            System.out.println("Sentencia sql incorrecta: " + sql);
            throw ex;
        } catch (MySQLIntegrityConstraintViolationException e)
        { 
            System.out.println(e.getMessage());  throw e; 
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            return 2;
        }
    }
    
    //para las consultas
    public ResultSet RunSql(String sql) throws MySQLSyntaxErrorException
    {
        try {
            if (connection == null)
                return null;
            System.out.println("SQL :> " + sql);
            Statement s = connection.createStatement();
            return s.executeQuery( sql );
        } catch (MySQLSyntaxErrorException ex) {
            System.out.println("Sentencia sql incorrecta: " + sql);
            throw ex;            
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }
        
    //devuelve el nombre del atributo al que hace referencia el metodo
    // por ejemplo, para getNombre, devuelve nombre
    public static String getAttribNameOf(String methodName)
    {
        if (methodName.startsWith("get") && methodName.length() > 3)
        {
            String attribName = methodName.substring(3, 4).toLowerCase() + methodName.substring(4);
            return attribName;
        }
        return null;
    }
    
    //devuelve true si existe el metodo en determinada clase
    public static boolean existsAttribOfMethod(Class classObj, String methodName)
    {
        String attribName = getAttribNameOf(methodName);
        if (attribName != null)
        {           
            try {
                if (classObj.getDeclaredField(attribName) != null)
                    return true;
            } catch (NoSuchFieldException ex) {
                return false;
            } catch (SecurityException ex) {
                ex.printStackTrace();
            }
        }
        return false;
    }
    
    
    
    
    public int insertion(Object obj) throws MySQLIntegrityConstraintViolationException
    {
        //System.out.println("Nombre clase: " + obj.getClass().getName());    
        Class classObj = obj.getClass();
      
        String attribs = "(";//para almanecar el nombre de los atributos
        String values = "(";//para almacenar los valores de los atributos
        for (Method method : classObj.getDeclaredMethods())
        {
            try {
                String methodName = method.getName();

                if (existsAttribOfMethod(classObj, methodName))
                {
                    /*System.out.println(" - Nombre metodo: " + method.getName());
                    System.out.println("   - Tipo: " + method.getReturnType().getName());
                    System.out.println("   - Valor: " + method.invoke(obj));
                    System.out.println("   - Atributo: " + getAttribNameOf(methodName) );
                    */
                    attribs += getAttribNameOf(methodName) + ",";
                    values += "'" + method.invoke(obj) + "',";
                }
                //if (method.getName().startsWith("setNombres"))
                //{
                //    method.invoke(obj, "TK");
                //}
            } catch (SecurityException ex) {
                ex.printStackTrace();                
            } catch (IllegalAccessException ex) {
                ex.printStackTrace();
            } catch (IllegalArgumentException ex) {
                ex.printStackTrace();
            } catch (InvocationTargetException ex) {
                ex.printStackTrace();
            }
        }
        
        //construimos la sentencia sql
        String sql = "insert into " + classObj.getSimpleName().toLowerCase() + " " + 
                attribs.substring(0,attribs.length()-1) + ") values" +
                values.substring(0,values.length()-1) + ")";
        
        System.out.println("SQL > " + sql);
        try {
            return execSql(sql);
        } catch (MySQLSyntaxErrorException ex) {
            return 1;
        }        
    }
    public int insertionWithout(Object obj, String sinEsteCampo) throws MySQLIntegrityConstraintViolationException
    {
        //System.out.println("Nombre clase: " + obj.getClass().getName());    
        Class classObj = obj.getClass();
      
        String attribs = "(";//para almanecar el nombre de los atributos
        String values = "(";//para almacenar los valores de los atributos
        for (Method method : classObj.getDeclaredMethods())
        {
            try {
                String methodName = method.getName();

                if (existsAttribOfMethod(classObj, methodName))
                {
                    /*System.out.println(" - Nombre metodo: " + method.getName());
                    System.out.println("   - Tipo: " + method.getReturnType().getName());
                    System.out.println("   - Valor: " + method.invoke(obj));
                    System.out.println("   - Atributo: " + getAttribNameOf(methodName) );
                    */
                    
                    String atributo = getAttribNameOf(methodName);
                    if (atributo.compareToIgnoreCase(sinEsteCampo) != 0)
                    {
                        attribs += getAttribNameOf(methodName) + ",";
                        values += "'" + method.invoke(obj) + "',";
                    }
                    

                }
                //if (method.getName().startsWith("setNombres"))
                //{
                //    method.invoke(obj, "TK");
                //}
            } catch (SecurityException ex) {
                ex.printStackTrace();                
            } catch (IllegalAccessException ex) {
                ex.printStackTrace();
            } catch (IllegalArgumentException ex) {
                ex.printStackTrace();
            } catch (InvocationTargetException ex) {
                ex.printStackTrace();
            }
        }        
        //construimos la sentencia sql
        String sql = "insert into " + classObj.getSimpleName().toLowerCase() + " " + 
                attribs.substring(0,attribs.length()-1) + ") values" +
                values.substring(0,values.length()-1) + ")";
        
        System.out.println("SQL >> " + sql);
        try {
            return execSql(sql);
        } catch (MySQLSyntaxErrorException ex) {
            return 1;
        }
    }

    //obtiene el nombre de la funcion Get del atributo
    public static String getGetterOf(String attrib)
    {
        return "get" + attrib.substring(0, 1).toUpperCase() + attrib.substring(1);        
    }

    //obtiene el nombre de la funcion Set del atributo
    public static String getSetterOf(String attrib)
    {
        return "set" + attrib.substring(0,1).toUpperCase() + attrib.substring(1);
    }

    
    //realiza una actualizacion a una tabla, se le pasa como parametor el nombre
    // del atributo que esta como primary key
    public int update(String pkName, Object obj)
    {
        //System.out.println("Nombre clase: " + obj.getClass().getName());    
        Class classObj = obj.getClass();
            
        Method pkMethod = null;
        String pkValue = "";
        String getterName = getGetterOf(pkName);
        
        try {            
            pkMethod = classObj.getDeclaredMethod(getterName);
            pkValue = pkMethod.invoke(obj).toString();
        } catch (IllegalAccessException ex) {
            return 1;
        } catch (IllegalArgumentException ex) {
            return 2;
        } catch (InvocationTargetException ex) {
            return 3;
        } catch (NoSuchMethodException ex) {
            return 4;
        } catch (SecurityException ex) {
            return 5;            
        }
        
        String updateSets = "";//para almacenar: atributo = valor
        for (Method method : classObj.getDeclaredMethods())
        {
            try {
                String methodName = method.getName();
                String attribName = getAttribNameOf(methodName);

                if (attribName != null && !attribName.equals(pkValue) && existsAttribOfMethod(classObj, methodName))                    
                    updateSets += attribName + " = '" + method.invoke(obj) + "', ";
            } catch (SecurityException ex) {
                ex.printStackTrace();                
            } catch (IllegalAccessException ex) {
                ex.printStackTrace();
            } catch (IllegalArgumentException ex) {
                ex.printStackTrace();
            } catch (InvocationTargetException ex) {
                ex.printStackTrace();
            }
        }
        updateSets = updateSets.substring(0,updateSets.length()-2);//quitamos el espacio y coma final
        
        //creamos la sentencia sql
        String sql = "update " + classObj.getSimpleName().toLowerCase() + " set " + updateSets +
                " where " + pkName + " = '" + pkValue + "'";        
        try {
            return execSql(sql);
        } catch (MySQLSyntaxErrorException ex) {
            return 6;
        } catch (MySQLIntegrityConstraintViolationException ex)
        {
            return 7;
        }
    }

    //para eliminacion, se pasa como parametro la clase primaria
    public int removing(String pkName, Object obj)
    {
        Class classObj = obj.getClass();
            
        Method pkMethod = null;
        String pkValue = "";
        String getterName = getGetterOf(pkName);
        
        try {            
            pkMethod = classObj.getDeclaredMethod(getterName);
            pkValue = pkMethod.invoke(obj).toString();
        } catch (IllegalAccessException ex) {
            return 1;
        } catch (IllegalArgumentException ex) {
            return 2;
        } catch (InvocationTargetException ex) {
            return 3;
        } catch (NoSuchMethodException ex) {
            return 4;
        } catch (SecurityException ex) {
            return 5;            
        }
        
        String sql = "delete from " + classObj.getSimpleName().toLowerCase() + 
                " where " + pkName + " = '" + pkValue + "'";        
        try {
            return execSql(sql);
        } catch (MySQLSyntaxErrorException ex) {
            return 6;
        } catch (MySQLIntegrityConstraintViolationException ex)
        { return 7; }
    }

    //para realizar consultas, se pasa el atributo de la clase a usar como busqueda y la clase
    public ResultSet search(String attrib2Use, Object obj)
    {
        //System.out.println("Nombre clase: " + obj.getClass().getName());    
        Class classObj = obj.getClass();

        String attribs = "";
        
        Method attribMethod = null;
        String attribValue = "";
        String getterName = getGetterOf(attrib2Use);
        
        //System.out.println("getter: " + getterName + " attrib: " + attrib2Use);
        
        try {                        
            attribMethod = classObj.getDeclaredMethod(getterName);
            attribValue = attribMethod.invoke(obj).toString();
        } catch (IllegalAccessException ex) {
            return null;
        } catch (IllegalArgumentException ex) {
            return null;
        } catch (InvocationTargetException ex) {
            return null;
        } catch (NoSuchMethodException ex) {
            return null;
        } catch (SecurityException ex) {
            return null;
        }
                
        for (Method method : classObj.getDeclaredMethods())
        {
            try {
                String methodName = method.getName();
                String attribName = getAttribNameOf(methodName);
                
                if (existsAttribOfMethod(classObj, methodName))                    
                    attribs += attribName + ",";
            } catch (SecurityException ex) {
                ex.printStackTrace();                
            } catch (IllegalArgumentException ex) {
                ex.printStackTrace();
            }
        }
        attribs = attribs.substring(0,attribs.length()-1);
        
        String sql = "select " + attribs + " from " + classObj.getSimpleName().toLowerCase() +
                " where " + attrib2Use + " like '" + attribValue + "'";
        try {
            return RunSql(sql);
        } catch (MySQLSyntaxErrorException ex) {
            return null;
        }
    }

    public ResultSet search(String attrib2Use, Object obj, String attrib2Use2, Object obj2)
    {
        //System.out.println("Nombre clase: " + obj.getClass().getName());    
        Class classObj = obj.getClass();

        String attribs = "";
        
        Method attribMethod = null;
        String attribValue = "";
        String getterName = getGetterOf(attrib2Use);
        
        //System.out.println("getter: " + getterName + " attrib: " + attrib2Use);
        
        try {                        
            attribMethod = classObj.getDeclaredMethod(getterName);
            attribValue = attribMethod.invoke(obj).toString();
        } catch (IllegalAccessException ex) {
            return null;
        } catch (IllegalArgumentException ex) {
            return null;
        } catch (InvocationTargetException ex) {
            return null;
        } catch (NoSuchMethodException ex) {
            return null;
        } catch (SecurityException ex) {
            return null;
        }
                
        for (Method method : classObj.getDeclaredMethods())
        {
            try {
                String methodName = method.getName();
                String attribName = getAttribNameOf(methodName);
                
                if (existsAttribOfMethod(classObj, methodName))                    
                    attribs += attribName + ",";
            } catch (SecurityException ex) {
                ex.printStackTrace();                
            } catch (IllegalArgumentException ex) {
                ex.printStackTrace();
            }
        }
        attribs = attribs.substring(0,attribs.length()-1);
        
        String sql = "select " + attribs + " from " + classObj.getSimpleName().toLowerCase() +
                " where " + attrib2Use + " like '" + attribValue + "'";
        try {
            return RunSql(sql);
        } catch (MySQLSyntaxErrorException ex) {
            return null;
        }
    }
    
    
}
